﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for HotelManagement
/// </summary>
public class HotelManagement
{
    #region const declaration
    public const string TABLE_NAME = "Hotels";
    public const string HOTELID = "hotelID";
    public const string HOTELNAME = "hotelName";
    public const string PRICE = "price";
    public const string START = "star";
    public const string ROOM = "room";
    public const string ADDRESS = "address";
    public const string PHONE = "phone";
    public const string HOTELDESCRIPTION = "hotelDescription";
    #endregion


	public HotelManagement()
	{
	}

    public static bool AddHotel(string hotelName, float price, int star, int room, string address, string phone, string description, List<string> pathLink)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();
   
        string sqlQuery = "INSERT INTO Hotels Values(@hotelName,@price,@star,@room,@address,@phone,@description);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@hotelName", hotelName);
        command.Parameters.AddWithValue("@price", price);
        command.Parameters.AddWithValue("@star", star);
        command.Parameters.AddWithValue("@room", room);
        command.Parameters.AddWithValue("@address", address);
        command.Parameters.AddWithValue("@phone", phone);
        command.Parameters.AddWithValue("@description", description);

        int n = int.Parse(command.ExecuteScalar().ToString());

        cnn.Close();

        if (n > 0)
        {
            //add images for hotel
            for (int i = 0; i < pathLink.Count; i++)
                ImageManagement.AddImg(pathLink[i], Common.IMAGE_HOTEL, n);

            return true;
        }
        else
            return false;
    }

    public static bool EditHotel(int hotelID, string hotelName, float price, int star, int room, string address, string phone, string description)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Hotels Set hotelName =@hotelName,price=@price,star=@star,room=@room,address=@address,phone=@phone,hotelDescription=@description WHERE hotelID=@hotelID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@hotelName", hotelName);
        command.Parameters.AddWithValue("@price", price);
        command.Parameters.AddWithValue("@star", star);
        command.Parameters.AddWithValue("@room", room);
        command.Parameters.AddWithValue("@address", address);
        command.Parameters.AddWithValue("@phone", phone);
        command.Parameters.AddWithValue("@description", description);
        command.Parameters.AddWithValue("@hotelID", hotelID);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteHotel(int hotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Hotels WHERE hotelID=@hotelID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@hotelID", hotelID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllHotel()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT * FROM Hotels ORDER BY star DESC, price ASC";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY star DESC, price ASC) AS NO, H.* FROM Hotels H) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllHotel = new DataSet();
        adapter.Fill(dsAllHotel, "AllHotel");

        return dsAllHotel;
    }

    public static DataSet GetAllOtherHotel(int hotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        string sqlQuery = "SELECT * FROM Hotels WHERE hotelID not like '" + hotelID + "' ORDER BY star DESC, price ASC";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);

        DataSet dsHotels = new DataSet();
        adapter.Fill(dsHotels, "Hotels");

        return dsHotels;
    }

    public static DataSet GetHotelByID(int hotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        string sqlQuery = "SELECT * FROM Hotels WHERE hotelID='" + hotelID + "'";

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, strCon);
        DataSet dsHotels = new DataSet();
        adapter.Fill(dsHotels, "HotelByID");
        return dsHotels;
    }

    public static DataSet GetTopHotelFromPR()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        SqlCommand command = new SqlCommand("PR_TOP_HOTEL", cnn);
        command.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter adapter = new SqlDataAdapter(command);

        DataSet dsAllHotel = new DataSet();
        adapter.Fill(dsAllHotel, "TopHotel");

        return dsAllHotel;
    }

    public static DataSet GetAllHotelFromPR(DataTable dtHotel)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        SqlCommand command = new SqlCommand("PR_LIST_HOTEL", cnn);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@listHotel", dtHotel);

        SqlDataAdapter adapter = new SqlDataAdapter(command);

        DataSet dsAllHotel = new DataSet();
        adapter.Fill(dsAllHotel, "AllHotel");

        return dsAllHotel;
    }

    public static DataSet SearchHotel(DateTime departDate, DateTime returnDate)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT * FROM Hotels ";
        string strWhere = " WHERE 1=1 ";
        
        if (departDate != null && departDate != DateTime.MinValue)
            strWhere += " AND CONVERT(varchar(20),T.startDate,112) >= " + Util.ConvertDateToString(departDate);
        if (returnDate != null && returnDate != DateTime.MinValue)
            strWhere += " AND CONVERT(varchar(20),T.endDate,112) <= " + Util.ConvertDateToString(returnDate);

        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery + strWhere, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "TourSearch");

        return dsAllTour;
    }

    public static DataSet SearchHotel(string departDate, string returnDate, int Room)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        SqlCommand command = new SqlCommand("PR_SEARCH_HOTEL", cnn);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@departDate", departDate);
        command.Parameters.Add("@returnDate", returnDate);
        command.Parameters.Add("@room", Room);

        SqlDataAdapter adapter = new SqlDataAdapter(command);

        DataSet dsSearchHotel = new DataSet();
        adapter.Fill(dsSearchHotel, "SearchHotel");

        return dsSearchHotel;
    }
}